<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chapter 3 Tutorial</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="installing.html" title="Chapter 2 Installing and Upgrading MySQL" />
<link rel="next" href="programs.html" title="Chapter 4 MySQL Programs" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 3 Tutorial</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="installing.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="programs.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="tutorial"></a>Chapter 3 Tutorial</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="tutorial.html#connecting-disconnecting">3.1 Connecting to and Disconnecting from the Server</a></span></dt><dt><span class="section"><a href="tutorial.html#entering-queries">3.2 Entering Queries</a></span></dt><dt><span class="section"><a href="tutorial.html#database-use">3.3 Creating and Using a Database</a></span></dt><dd><dl><dt><span class="section"><a href="tutorial.html#creating-database">3.3.1 Creating and Selecting a Database</a></span></dt><dt><span class="section"><a href="tutorial.html#creating-tables">3.3.2 Creating a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#loading-tables">3.3.3 Loading Data into a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#retrieving-data">3.3.4 Retrieving Information from a Table</a></span></dt></dl></dd><dt><span class="section"><a href="tutorial.html#getting-information">3.4 Getting Information About Databases and Tables</a></span></dt><dt><span class="section"><a href="tutorial.html#batch-mode">3.5 Using mysql in Batch Mode</a></span></dt><dt><span class="section"><a href="tutorial.html#examples">3.6 Examples of Common Queries</a></span></dt><dd><dl><dt><span class="section"><a href="tutorial.html#example-maximum-column">3.6.1 The Maximum Value for a Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-row">3.6.2 The Row Holding the Maximum of a Certain Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group">3.6.3 Maximum of Column per Group</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group-row">3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-user-variables">3.6.5 Using User-Defined Variables</a></span></dt><dt><span class="section"><a href="tutorial.html#example-foreign-keys">3.6.6 Using Foreign Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#searching-on-two-keys">3.6.7 Searching on Two Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#calculating-days">3.6.8 Calculating Visits Per Day</a></span></dt><dt><span class="section"><a href="tutorial.html#example-auto-increment">3.6.9 Using AUTO_INCREMENT</a></span></dt></dl></dd><dt><span class="section"><a href="tutorial.html#apache">3.7 Using MySQL with Apache</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091788411760"></a><a class="indexterm" name="idm140091788410688"></a><a class="indexterm" name="idm140091788409200"></a><a class="indexterm" name="idm140091788407712"></a><p>
    This chapter provides a tutorial introduction to MySQL by showing
    how to use the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> client program to create and
    use a simple database. <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> (sometimes referred
    to as the <span class="quote">“<span class="quote">terminal monitor</span>”</span> or just
    <span class="quote">“<span class="quote">monitor</span>”</span>) is an interactive program that enables you
    to connect to a MySQL server, run queries, and view the results.
    <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> may also be used in batch mode: you place
    your queries in a file beforehand, then tell
    <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> to execute the contents of the file. Both
    ways of using <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> are covered here.
  </p><p>
    To see a list of options provided by <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>,
    invoke it with the <a class="link" href="programs.html#option_mysql_help"><code class="option">--help</code></a> option:
  </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql --help</code></strong>
</pre><p>
    This chapter assumes that <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is installed on
    your machine and that a MySQL server is available to which you can
    connect. If this is not true, contact your MySQL administrator. (If
    <span class="emphasis"><em>you</em></span> are the administrator, you need to consult
    the relevant portions of this manual, such as
    <a class="xref" href="server-administration.html" title="Chapter 5 MySQL Server Administration">Chapter 5, <i>MySQL Server Administration</i></a>.)
  </p><p>
    This chapter describes the entire process of setting up and using a
    database. If you are interested only in accessing an existing
    database, you may want to skip the sections that describe how to
    create the database and the tables it contains.
  </p><p>
    Because this chapter is tutorial in nature, many details are
    necessarily omitted. Consult the relevant sections of the manual for
    more information on the topics covered here.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="connecting-disconnecting"></a>3.1 Connecting to and Disconnecting from the Server</h2>
</div>
</div>
</div>
<a class="indexterm" name="idm140091788388480"></a><a class="indexterm" name="idm140091788386992"></a><a class="indexterm" name="idm140091788385504"></a><a class="indexterm" name="idm140091788384016"></a><p>
      To connect to the server, you will usually need to provide a MySQL
      user name when you invoke <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> and, most
      likely, a password. If the server runs on a machine other than the
      one where you log in, you will also need to specify a host name.
      Contact your administrator to find out what connection parameters
      you should use to connect (that is, what host, user name, and
      password to use). Once you know the proper parameters, you should
      be able to connect like this:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p</code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
</pre><p>
      <em class="replaceable"><code>host</code></em> and
      <em class="replaceable"><code>user</code></em> represent the host name where your
      MySQL server is running and the user name of your MySQL account.
      Substitute appropriate values for your setup. The
      <code class="literal">********</code> represents your password; enter it
      when <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> displays the <code class="literal">Enter
      password:</code> prompt.
    </p><p>
      If that works, you should see some introductory information
      followed by a <code class="literal">mysql&gt;</code> prompt:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p</code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 8.0.18-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql&gt;
</pre><p>
      The <code class="literal">mysql&gt;</code> prompt tells you that
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is ready for you to enter SQL statements.
    </p><p>
      If you are logging in on the same machine that MySQL is running
      on, you can omit the host, and simply use the following:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql -u <em class="replaceable"><code>user</code></em> -p</code></strong>
</pre><p>
      If, when you attempt to log in, you get an error message such as
      <span class="errortext">ERROR 2002 (HY000): Can't connect to local MySQL server
      through socket '/tmp/mysql.sock' (2)</span>, it means that
      the MySQL server daemon (Unix) or service (Windows) is not
      running. Consult the administrator or see the section of
      <a class="xref" href="installing.html" title="Chapter 2 Installing and Upgrading MySQL">Chapter 2, <i>Installing and Upgrading MySQL</i></a> that is appropriate to your operating
      system.
    </p><p>
      For help with other problems often encountered when trying to log
      in, see <a class="xref" href="error-handling.html#common-errors" title="B.4.2 Common Errors When Using MySQL Programs">Section B.4.2, “Common Errors When Using MySQL Programs”</a>.
    </p><p>
      Some MySQL installations permit users to connect as the anonymous
      (unnamed) user to the server running on the local host. If this is
      the case on your machine, you should be able to connect to that
      server by invoking <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> without any options:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql</code></strong>
</pre><p>
      After you have connected successfully, you can disconnect any time
      by typing <code class="literal">QUIT</code> (or <code class="literal">\q</code>) at
      the <code class="literal">mysql&gt;</code> prompt:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>QUIT</code></strong>
Bye
</pre><p>
      On Unix, you can also disconnect by pressing Control+D.
    </p><p>
      Most examples in the following sections assume that you are
      connected to the server. They indicate this by the
      <code class="literal">mysql&gt;</code> prompt.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="entering-queries"></a>3.2 Entering Queries</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091788349344"></a><a class="indexterm" name="idm140091788347856"></a><a class="indexterm" name="idm140091788346368"></a><p>
      Make sure that you are connected to the server, as discussed in
      the previous section. Doing so does not in itself select any
      database to work with, but that is okay. At this point, it is more
      important to find out a little about how to issue queries than to
      jump right in creating tables, loading data into them, and
      retrieving data from them. This section describes the basic
      principles of entering queries, using several queries you can try
      out to familiarize yourself with how <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
      works.
    </p><p>
      Here is a simple query that asks the server to tell you its
      version number and the current date. Type it in as shown here
      following the <code class="literal">mysql&gt;</code> prompt and press Enter:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT VERSION(), CURRENT_DATE;</code></strong>
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.8.0-m17 | 2015-12-21   |
+-----------+--------------+
1 row in set (0.02 sec)
mysql&gt;
</pre><p>
      This query illustrates several things about
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          A query normally consists of an SQL statement followed by a
          semicolon. (There are some exceptions where a semicolon may be
          omitted. <code class="literal">QUIT</code>, mentioned earlier, is one of
          them. We'll get to others later.)
        </p></li><li class="listitem"><p>
          When you issue a query, <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> sends it to
          the server for execution and displays the results, then prints
          another <code class="literal">mysql&gt;</code> prompt to indicate that
          it is ready for another query.
        </p></li><li class="listitem"><p>
          <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> displays query output in tabular form
          (rows and columns). The first row contains labels for the
          columns. The rows following are the query results. Normally,
          column labels are the names of the columns you fetch from
          database tables. If you're retrieving the value of an
          expression rather than a table column (as in the example just
          shown), <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> labels the column using the
          expression itself.
        </p></li><li class="listitem"><p>
          <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> shows how many rows were returned and
          how long the query took to execute, which gives you a rough
          idea of server performance. These values are imprecise because
          they represent wall clock time (not CPU or machine time), and
          because they are affected by factors such as server load and
          network latency. (For brevity, the <span class="quote">“<span class="quote">rows in set</span>”</span>
          line is sometimes not shown in the remaining examples in this
          chapter.)
</p></li></ul>
</div>
<p>
      Keywords may be entered in any lettercase. The following queries
      are equivalent:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT VERSION(), CURRENT_DATE;</code></strong>
mysql&gt; <strong class="userinput"><code>select version(), current_date;</code></strong>
mysql&gt; <strong class="userinput"><code>SeLeCt vErSiOn(), current_DATE;</code></strong>
</pre><p>
      Here is another query. It demonstrates that you can use
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> as a simple calculator:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT SIN(PI()/4), (4+1)*5;</code></strong>
+------------------+---------+
| SIN(PI()/4)      | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 |      25 |
+------------------+---------+
1 row in set (0.02 sec)
</pre><p>
      The queries shown thus far have been relatively short, single-line
      statements. You can even enter multiple statements on a single
      line. Just end each one with a semicolon:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT VERSION(); SELECT NOW();</code></strong>
+-----------+
| VERSION() |
+-----------+
| 8.0.13    |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2018-08-24 00:56:40 |
+---------------------+
1 row in set (0.00 sec)
</pre><p>
      A query need not be given all on a single line, so lengthy queries
      that require several lines are not a problem.
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> determines where your statement ends by
      looking for the terminating semicolon, not by looking for the end
      of the input line. (In other words, <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
      accepts free-format input: it collects input lines but does not
      execute them until it sees the semicolon.)
    </p><p>
      Here is a simple multiple-line statement:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT</code></strong>
    -&gt; <strong class="userinput"><code>USER()</code></strong>
    -&gt; <strong class="userinput"><code>,</code></strong>
    -&gt; <strong class="userinput"><code>CURRENT_DATE;</code></strong>
+---------------+--------------+
| USER()        | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2018-08-24   |
+---------------+--------------+
</pre><p>
      In this example, notice how the prompt changes from
      <code class="literal">mysql&gt;</code> to <code class="literal">-&gt;</code> after you
      enter the first line of a multiple-line query. This is how
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> indicates that it has not yet seen a
      complete statement and is waiting for the rest. The prompt is your
      friend, because it provides valuable feedback. If you use that
      feedback, you can always be aware of what <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
      is waiting for.
    </p><p>
      If you decide you do not want to execute a query that you are in
      the process of entering, cancel it by typing
      <code class="literal">\c</code>:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT</code></strong>
    -&gt; <strong class="userinput"><code>USER()</code></strong>
    -&gt; <strong class="userinput"><code>\c</code></strong>
mysql&gt;
</pre><p>
      Here, too, notice the prompt. It switches back to
      <code class="literal">mysql&gt;</code> after you type <code class="literal">\c</code>,
      providing feedback to indicate that <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is
      ready for a new query.
    </p><p>
      The following table shows each of the prompts you may see and
      summarizes what they mean about the state that
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is in.
</p><a class="indexterm" name="idm140091788295072"></a>
<div class="informaltable">
<table summary="MySQL prompts and the meaning of each prompt."><col width="10%"><col width="80%"><thead><tr>
          <th scope="col">Prompt</th>
          <th scope="col">Meaning</th>
        </tr></thead><tbody><tr>
          <td scope="row"><code class="literal">mysql&gt;</code></td>
          <td>Ready for new query</td>
        </tr><tr>
          <td scope="row"><code class="literal">-&gt;</code></td>
          <td>Waiting for next line of multiple-line query</td>
        </tr><tr>
          <td scope="row"><code class="literal">'&gt;</code></td>
          <td>Waiting for next line, waiting for completion of a string that began
            with a single quote (<code class="literal">'</code>)</td>
        </tr><tr>
          <td scope="row"><code class="literal">"&gt;</code></td>
          <td>Waiting for next line, waiting for completion of a string that began
            with a double quote (<code class="literal">"</code>)</td>
        </tr><tr>
          <td scope="row"><code class="literal">`&gt;</code></td>
          <td>Waiting for next line, waiting for completion of an identifier that
            began with a backtick (<code class="literal">`</code>)</td>
        </tr><tr>
          <td scope="row"><code class="literal">/*&gt;</code></td>
          <td>Waiting for next line, waiting for completion of a comment that began
            with <code class="literal">/*</code></td>
</tr></tbody></table>
</div>
<p>
      Multiple-line statements commonly occur by accident when you
      intend to issue a query on a single line, but forget the
      terminating semicolon. In this case, <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
      waits for more input:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT USER()</code></strong>
    -&gt;
</pre><p>
      If this happens to you (you think you've entered a statement but
      the only response is a <code class="literal">-&gt;</code> prompt), most
      likely <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is waiting for the semicolon. If
      you don't notice what the prompt is telling you, you might sit
      there for a while before realizing what you need to do. Enter a
      semicolon to complete the statement, and <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
      executes it:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT USER()</code></strong>
    -&gt; <strong class="userinput"><code>;</code></strong>
+---------------+
| USER()        |
+---------------+
| jon@localhost |
+---------------+
</pre><p>
      The <code class="literal">'&gt;</code> and <code class="literal">"&gt;</code> prompts
      occur during string collection (another way of saying that MySQL
      is waiting for completion of a string). In MySQL, you can write
      strings surrounded by either <code class="literal">'</code> or
      <code class="literal">"</code> characters (for example,
      <code class="literal">'hello'</code> or <code class="literal">"goodbye"</code>), and
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> lets you enter strings that span multiple
      lines. When you see a <code class="literal">'&gt;</code> or
      <code class="literal">"&gt;</code> prompt, it means that you have entered a
      line containing a string that begins with a <code class="literal">'</code>
      or <code class="literal">"</code> quote character, but have not yet entered
      the matching quote that terminates the string. This often
      indicates that you have inadvertently left out a quote character.
      For example:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM my_table WHERE name = 'Smith AND age &lt; 30;</code></strong>
    '&gt;
</pre><p>
      If you enter this <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement,
      then press <span class="keycap"><strong>Enter</strong></span> and wait for the result, nothing
      happens. Instead of wondering why this query takes so long, notice
      the clue provided by the <code class="literal">'&gt;</code> prompt. It tells
      you that <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> expects to see the rest of an
      unterminated string. (Do you see the error in the statement? The
      string <code class="literal">'Smith</code> is missing the second single
      quotation mark.)
    </p><p>
      At this point, what do you do? The simplest thing is to cancel the
      query. However, you cannot just type <code class="literal">\c</code> in this
      case, because <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> interprets it as part of
      the string that it is collecting. Instead, enter the closing quote
      character (so <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> knows you've finished the
      string), then type <code class="literal">\c</code>:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM my_table WHERE name = 'Smith AND age &lt; 30;</code></strong>
    '&gt; <strong class="userinput"><code>'\c</code></strong>
mysql&gt;
</pre><p>
      The prompt changes back to <code class="literal">mysql&gt;</code>,
      indicating that <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is ready for a new query.
    </p><p>
      The <code class="literal">`&gt;</code> prompt is similar to the
      <code class="literal">'&gt;</code> and <code class="literal">"&gt;</code> prompts, but
      indicates that you have begun but not completed a backtick-quoted
      identifier.
    </p><p>
      It is important to know what the <code class="literal">'&gt;</code>,
      <code class="literal">"&gt;</code>, and <code class="literal">`&gt;</code> prompts
      signify, because if you mistakenly enter an unterminated string,
      any further lines you type appear to be ignored by
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>—including a line containing
      <code class="literal">QUIT</code>. This can be quite confusing, especially
      if you do not know that you need to supply the terminating quote
      before you can cancel the current query.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
        Multiline statements from this point on are written without the
        secondary (<code class="literal">-&gt;</code> or other) prompts, to make
        it easier to copy and paste the statements to try for yourself.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="database-use"></a>3.3 Creating and Using a Database</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="tutorial.html#creating-database">3.3.1 Creating and Selecting a Database</a></span></dt><dt><span class="section"><a href="tutorial.html#creating-tables">3.3.2 Creating a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#loading-tables">3.3.3 Loading Data into a Table</a></span></dt><dt><span class="section"><a href="tutorial.html#retrieving-data">3.3.4 Retrieving Information from a Table</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091788222176"></a><a class="indexterm" name="idm140091788220720"></a><a class="indexterm" name="idm140091788219232"></a><p>
      Once you know how to enter SQL statements, you are ready to access
      a database.
    </p><p>
      Suppose that you have several pets in your home (your menagerie)
      and you would like to keep track of various types of information
      about them. You can do so by creating tables to hold your data and
      loading them with the desired information. Then you can answer
      different sorts of questions about your animals by retrieving data
      from the tables. This section shows you how to perform the
      following operations:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Create a database
        </p></li><li class="listitem"><p>
          Create a table
        </p></li><li class="listitem"><p>
          Load data into the table
        </p></li><li class="listitem"><p>
          Retrieve data from the table in various ways
        </p></li><li class="listitem"><p>
          Use multiple tables
</p></li></ul>
</div>
<p>
      The menagerie database is simple (deliberately), but it is not
      difficult to think of real-world situations in which a similar
      type of database might be used. For example, a database like this
      could be used by a farmer to keep track of livestock, or by a
      veterinarian to keep track of patient records. A menagerie
      distribution containing some of the queries and sample data used
      in the following sections can be obtained from the MySQL website.
      It is available in both compressed <span class="command"><strong>tar</strong></span> file and
      Zip formats at <a class="ulink" href="https://dev.mysql.com/doc/" target="_top">https://dev.mysql.com/doc/</a>.
    </p><p>
      Use the <a class="link" href="sql-syntax.html#show" title="13.7.6 SHOW Syntax"><code class="literal">SHOW</code></a> statement to find out
      what databases currently exist on the server:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW DATABASES;</code></strong>
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+
</pre><p>
      The <code class="literal">mysql</code> database describes user access
      privileges. The <code class="literal">test</code> database often is
      available as a workspace for users to try things out.
    </p><p>
      The list of databases displayed by the statement may be different
      on your machine; <a class="link" href="sql-syntax.html#show-databases" title="13.7.6.14 SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a>
      does not show databases that you have no privileges for if you do
      not have the <a class="link" href="sql-syntax.html#show-databases" title="13.7.6.14 SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a>
      privilege. See <a class="xref" href="sql-syntax.html#show-databases" title="13.7.6.14 SHOW DATABASES Syntax">Section 13.7.6.14, “SHOW DATABASES Syntax”</a>.
    </p><p>
      If the <code class="literal">test</code> database exists, try to access it:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>USE test</code></strong>
Database changed
</pre><p>
      <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a>, like <code class="literal">QUIT</code>,
      does not require a semicolon. (You can terminate such statements
      with a semicolon if you like; it does no harm.) The
      <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a> statement is special in another
      way, too: it must be given on a single line.
    </p><p>
      You can use the <code class="literal">test</code> database (if you have
      access to it) for the examples that follow, but anything you
      create in that database can be removed by anyone else with access
      to it. For this reason, you should probably ask your MySQL
      administrator for permission to use a database of your own.
      Suppose that you want to call yours <code class="literal">menagerie</code>.
      The administrator needs to execute a statement like this:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';</code></strong>
</pre><p>
      where <code class="literal">your_mysql_name</code> is the MySQL user name
      assigned to you and <code class="literal">your_client_host</code> is the
      host from which you connect to the server.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="creating-database"></a>3.3.1 Creating and Selecting a Database</h3>
</div>
</div>
</div>
<a class="indexterm" name="idm140091788186832"></a><a class="indexterm" name="idm140091788185376"></a><p>
        If the administrator creates your database for you when setting
        up your permissions, you can begin using it. Otherwise, you need
        to create it yourself:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE DATABASE menagerie;</code></strong>
</pre><p>
        Under Unix, database names are case-sensitive (unlike SQL
        keywords), so you must always refer to your database as
        <code class="literal">menagerie</code>, not as
        <code class="literal">Menagerie</code>, <code class="literal">MENAGERIE</code>, or
        some other variant. This is also true for table names. (Under
        Windows, this restriction does not apply, although you must
        refer to databases and tables using the same lettercase
        throughout a given query. However, for a variety of reasons, the
        recommended best practice is always to use the same lettercase
        that was used when the database was created.)
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          If you get an error such as <span class="errortext">ERROR 1044 (42000):
          Access denied for user 'micah'@'localhost' to database
          'menagerie'</span> when attempting to create a database,
          this means that your user account does not have the necessary
          privileges to do so. Discuss this with the administrator or
          see <a class="xref" href="security.html#access-control" title="6.2 Access Control and Account Management">Section 6.2, “Access Control and Account Management”</a>.
</p>
</div>
<p>
        Creating a database does not select it for use; you must do that
        explicitly. To make <code class="literal">menagerie</code> the current
        database, use this statement:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>USE menagerie</code></strong>
Database changed
</pre><p>
        Your database needs to be created only once, but you must select
        it for use each time you begin a <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
        session. You can do this by issuing a
        <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a> statement as shown in the
        example. Alternatively, you can select the database on the
        command line when you invoke <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>. Just
        specify its name after any connection parameters that you might
        need to provide. For example:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p menagerie</code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
</pre>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
          <code class="literal">menagerie</code> in the command just shown is
          <span class="bold"><strong>not</strong></span> your password. If you
          want to supply your password on the command line after the
          <code class="literal">-p</code> option, you must do so with no
          intervening space (for example, as
          <code class="literal">-p<em class="replaceable"><code>password</code></em></code>, not
          as <code class="literal">-p <em class="replaceable"><code>password</code></em></code>).
          However, putting your password on the command line is not
          recommended, because doing so exposes it to snooping by other
          users logged in on your machine.
</p>
</div>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<div class="admon-title">
Note
</div>
<p>
          You can see at any time which database is currently selected
          using <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>
          <a class="link" href="functions.html#function_database"><code class="literal">DATABASE()</code></a>.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="creating-tables"></a>3.3.2 Creating a Table</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091788156000"></a><a class="indexterm" name="idm140091788154512"></a><p>
        Creating the database is the easy part, but at this point it is
        empty, as <a class="link" href="sql-syntax.html#show-tables" title="13.7.6.37 SHOW TABLES Syntax"><code class="literal">SHOW TABLES</code></a> tells you:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW TABLES;</code></strong>
Empty set (0.00 sec)
</pre><p>
        The harder part is deciding what the structure of your database
        should be: what tables you need and what columns should be in
        each of them.
      </p><p>
        You want a table that contains a record for each of your pets.
        This can be called the <code class="literal">pet</code> table, and it
        should contain, as a bare minimum, each animal's name. Because
        the name by itself is not very interesting, the table should
        contain other information. For example, if more than one person
        in your family keeps pets, you might want to list each animal's
        owner. You might also want to record some basic descriptive
        information such as species and sex.
      </p><p>
        How about age? That might be of interest, but it is not a good
        thing to store in a database. Age changes as time passes, which
        means you'd have to update your records often. Instead, it is
        better to store a fixed value such as date of birth. Then,
        whenever you need age, you can calculate it as the difference
        between the current date and the birth date. MySQL provides
        functions for doing date arithmetic, so this is not difficult.
        Storing birth date rather than age has other advantages, too:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            You can use the database for tasks such as generating
            reminders for upcoming pet birthdays. (If you think this
            type of query is somewhat silly, note that it is the same
            question you might ask in the context of a business database
            to identify clients to whom you need to send out birthday
            greetings in the current week or month, for that
            computer-assisted personal touch.)
          </p></li><li class="listitem"><p>
            You can calculate age in relation to dates other than the
            current date. For example, if you store death date in the
            database, you can easily calculate how old a pet was when it
            died.
</p></li></ul>
</div>
<p>
        You can probably think of other types of information that would
        be useful in the <code class="literal">pet</code> table, but the ones
        identified so far are sufficient: name, owner, species, sex,
        birth, and death.
      </p><p>
        Use a <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement to
        specify the layout of your table:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),</code></strong>
       <strong class="userinput"><code>species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);</code></strong>
</pre><p>
        <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> is a good choice for the
        <code class="literal">name</code>, <code class="literal">owner</code>, and
        <code class="literal">species</code> columns because the column values
        vary in length. The lengths in those column definitions need not
        all be the same, and need not be <code class="literal">20</code>. You can
        normally pick any length from <code class="literal">1</code> to
        <code class="literal">65535</code>, whatever seems most reasonable to you.
        If you make a poor choice and it turns out later that you need a
        longer field, MySQL provides an <a class="link" href="sql-syntax.html#alter-table" title="13.1.9 ALTER TABLE Syntax"><code class="literal">ALTER
        TABLE</code></a> statement.
      </p><p>
        Several types of values can be chosen to represent sex in animal
        records, such as <code class="literal">'m'</code> and
        <code class="literal">'f'</code>, or perhaps <code class="literal">'male'</code> and
        <code class="literal">'female'</code>. It is simplest to use the single
        characters <code class="literal">'m'</code> and <code class="literal">'f'</code>.
      </p><p>
        The use of the <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATE</code></a> data type for
        the <code class="literal">birth</code> and <code class="literal">death</code>
        columns is a fairly obvious choice.
      </p><p>
        Once you have created a table, <a class="link" href="sql-syntax.html#show-tables" title="13.7.6.37 SHOW TABLES Syntax"><code class="literal">SHOW
        TABLES</code></a> should produce some output:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW TABLES;</code></strong>
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+
</pre><p>
        To verify that your table was created the way you expected, use
        a <a class="link" href="sql-syntax.html#describe" title="13.8.1 DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a> statement:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>DESCRIBE pet;</code></strong>
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
</pre><p>
        You can use <a class="link" href="sql-syntax.html#describe" title="13.8.1 DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a> any time,
        for example, if you forget the names of the columns in your
        table or what types they have.
      </p><p>
        For more information about MySQL data types, see
        <a class="xref" href="data-types.html" title="Chapter 11 Data Types">Chapter 11, <i>Data Types</i></a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="loading-tables"></a>3.3.3 Loading Data into a Table</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091788110704"></a><a class="indexterm" name="idm140091788109248"></a><a class="indexterm" name="idm140091788107760"></a><p>
        After creating your table, you need to populate it. The
        <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD DATA</code></a> and
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statements are useful for
        this.
      </p><p>
        Suppose that your pet records can be described as shown here.
        (Observe that MySQL expects dates in
        <code class="literal">'YYYY-MM-DD'</code> format; this may be different
        from what you are used to.)
</p>
<div class="informaltable">
<table summary="Example of pet records mentioned in the preceding text."><col width="10%"><col width="10%"><col width="10%"><col width="05%"><col width="20%"><col width="20%"><thead><tr>
            <th scope="col">name</th>
            <th scope="col">owner</th>
            <th scope="col">species</th>
            <th scope="col">sex</th>
            <th scope="col">birth</th>
            <th scope="col">death</th>
          </tr></thead><tbody><tr>
            <td scope="row">Fluffy</td>
            <td>Harold</td>
            <td>cat</td>
            <td>f</td>
            <td>1993-02-04</td>
            <td></td>
          </tr><tr>
            <td scope="row">Claws</td>
            <td>Gwen</td>
            <td>cat</td>
            <td>m</td>
            <td>1994-03-17</td>
            <td></td>
          </tr><tr>
            <td scope="row">Buffy</td>
            <td>Harold</td>
            <td>dog</td>
            <td>f</td>
            <td>1989-05-13</td>
            <td></td>
          </tr><tr>
            <td scope="row">Fang</td>
            <td>Benny</td>
            <td>dog</td>
            <td>m</td>
            <td>1990-08-27</td>
            <td></td>
          </tr><tr>
            <td scope="row">Bowser</td>
            <td>Diane</td>
            <td>dog</td>
            <td>m</td>
            <td>1979-08-31</td>
            <td>1995-07-29</td>
          </tr><tr>
            <td scope="row">Chirpy</td>
            <td>Gwen</td>
            <td>bird</td>
            <td>f</td>
            <td>1998-09-11</td>
            <td></td>
          </tr><tr>
            <td scope="row">Whistler</td>
            <td>Gwen</td>
            <td>bird</td>
            <td></td>
            <td>1997-12-09</td>
            <td></td>
          </tr><tr>
            <td scope="row">Slim</td>
            <td>Benny</td>
            <td>snake</td>
            <td>m</td>
            <td>1996-04-29</td>
            <td></td>
</tr></tbody></table>
</div>
<p>
        Because you are beginning with an empty table, an easy way to
        populate it is to create a text file containing a row for each
        of your animals, then load the contents of the file into the
        table with a single statement.
      </p><p>
        You could create a text file <code class="filename">pet.txt</code>
        containing one record per line, with values separated by tabs,
        and given in the order in which the columns were listed in the
        <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement. For
        missing values (such as unknown sexes or death dates for animals
        that are still living), you can use <code class="literal">NULL</code>
        values. To represent these in your text file, use
        <code class="literal">\N</code> (backslash, capital-N). For example, the
        record for Whistler the bird would look like this (where the
        whitespace between values is a single tab character):
      </p><pre data-lang="none" class="programlisting">
Whistler        Gwen    bird    \N      1997-12-09      \N
</pre><p>
        To load the text file <code class="filename">pet.txt</code> into the
        <code class="literal">pet</code> table, use this statement:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;</code></strong>
</pre><p>
        If you created the file on Windows with an editor that uses
        <code class="literal">\r\n</code> as a line terminator, you should use
        this statement instead:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet</code></strong>
       <strong class="userinput"><code>LINES TERMINATED BY '\r\n';</code></strong>
</pre><p>
        (On an Apple machine running OS X, you would likely want to use
        <code class="literal">LINES TERMINATED BY '\r'</code>.)
      </p><p>
        You can specify the column value separator and end of line
        marker explicitly in the <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD
        DATA</code></a> statement if you wish, but the defaults are tab
        and linefeed. These are sufficient for the statement to read the
        file <code class="filename">pet.txt</code> properly.
      </p><p>
        If the statement fails, it is likely that your MySQL
        installation does not have local file capability enabled by
        default. See <a class="xref" href="security.html#load-data-local" title="6.1.6 Security Issues with LOAD DATA LOCAL">Section 6.1.6, “Security Issues with LOAD DATA LOCAL”</a>, for information
        on how to change this.
      </p><p>
        When you want to add new records one at a time, the
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement is useful. In
        its simplest form, you supply values for each column, in the
        order in which the columns were listed in the
        <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement. Suppose
        that Diane gets a new hamster named <span class="quote">“<span class="quote">Puffball.</span>”</span> You
        could add a new record using an
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement like this:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>INSERT INTO pet</code></strong>
       <strong class="userinput"><code>VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);</code></strong>
</pre><p>
        String and date values are specified as quoted strings here.
        Also, with <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>, you can insert
        <code class="literal">NULL</code> directly to represent a missing value.
        You do not use <code class="literal">\N</code> like you do with
        <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD DATA</code></a>.
      </p><p>
        From this example, you should be able to see that there would be
        a lot more typing involved to load your records initially using
        several <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statements rather
        than a single <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD DATA</code></a>
        statement.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="retrieving-data"></a>3.3.4 Retrieving Information from a Table</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="tutorial.html#selecting-all">3.3.4.1 Selecting All Data</a></span></dt><dt><span class="section"><a href="tutorial.html#selecting-rows">3.3.4.2 Selecting Particular Rows</a></span></dt><dt><span class="section"><a href="tutorial.html#selecting-columns">3.3.4.3 Selecting Particular Columns</a></span></dt><dt><span class="section"><a href="tutorial.html#sorting-rows">3.3.4.4 Sorting Rows</a></span></dt><dt><span class="section"><a href="tutorial.html#date-calculations">3.3.4.5 Date Calculations</a></span></dt><dt><span class="section"><a href="tutorial.html#working-with-null">3.3.4.6 Working with NULL Values</a></span></dt><dt><span class="section"><a href="tutorial.html#pattern-matching">3.3.4.7 Pattern Matching</a></span></dt><dt><span class="section"><a href="tutorial.html#counting-rows">3.3.4.8 Counting Rows</a></span></dt><dt><span class="section"><a href="tutorial.html#multiple-tables">3.3.4.9 Using More Than one Table</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091788008320"></a><a class="indexterm" name="idm140091788006864"></a><a class="indexterm" name="idm140091788005376"></a><a class="indexterm" name="idm140091788003888"></a><p>
        The <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement is used to
        pull information from a table. The general form of the statement
        is:
      </p><pre data-lang="sql" class="programlisting">
SELECT <em class="replaceable"><code>what_to_select</code></em>
FROM <em class="replaceable"><code>which_table</code></em>
WHERE <em class="replaceable"><code>conditions_to_satisfy</code></em>;
</pre><p>
        <em class="replaceable"><code>what_to_select</code></em> indicates what you
        want to see. This can be a list of columns, or
        <code class="literal">*</code> to indicate <span class="quote">“<span class="quote">all columns.</span>”</span>
        <em class="replaceable"><code>which_table</code></em> indicates the table from
        which you want to retrieve data. The <code class="literal">WHERE</code>
        clause is optional. If it is present,
        <em class="replaceable"><code>conditions_to_satisfy</code></em> specifies one
        or more conditions that rows must satisfy to qualify for
        retrieval.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="selecting-all"></a>3.3.4.1 Selecting All Data</h4>
</div>
</div>
</div>
<p>
          The simplest form of <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>
          retrieves everything from a table:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet;</code></strong>
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
</pre><p>
          This form of <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> is useful
          if you want to review your entire table, for example, after
          you've just loaded it with your initial data set. For example,
          you may happen to think that the birth date for Bowser doesn't
          seem quite right. Consulting your original pedigree papers,
          you find that the correct birth year should be 1989, not 1979.
        </p><p>
          There are at least two ways to fix this:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              Edit the file <code class="filename">pet.txt</code> to correct the
              error, then empty the table and reload it using
              <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> and
              <a class="link" href="sql-syntax.html#load-data" title="13.2.7 LOAD DATA Syntax"><code class="literal">LOAD DATA</code></a>:
            </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>DELETE FROM pet;</code></strong>
mysql&gt; <strong class="userinput"><code>LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;</code></strong>
</pre><p>
              However, if you do this, you must also re-enter the record
              for Puffball.
            </p></li><li class="listitem"><p>
              Fix only the erroneous record with an
              <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a> statement:
            </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';</code></strong>
</pre><p>
              The <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a> changes only the
              record in question and does not require you to reload the
              table.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="selecting-rows"></a>3.3.4.2 Selecting Particular Rows</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787972608"></a><a class="indexterm" name="idm140091787971152"></a><p>
          As shown in the preceding section, it is easy to retrieve an
          entire table. Just omit the <code class="literal">WHERE</code> clause
          from the <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement. But
          typically you don't want to see the entire table, particularly
          when it becomes large. Instead, you're usually more interested
          in answering a particular question, in which case you specify
          some constraints on the information you want. Let's look at
          some selection queries in terms of questions about your pets
          that they answer.
        </p><p>
          You can select only particular rows from your table. For
          example, if you want to verify the change that you made to
          Bowser's birth date, select Bowser's record like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE name = 'Bowser';</code></strong>
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
</pre><p>
          The output confirms that the year is correctly recorded as
          1989, not 1979.
        </p><p>
          String comparisons normally are case-insensitive, so you can
          specify the name as <code class="literal">'bowser'</code>,
          <code class="literal">'BOWSER'</code>, and so forth. The query result is
          the same.
        </p><p>
          You can specify conditions on any column, not just
          <code class="literal">name</code>. For example, if you want to know
          which animals were born during or after 1998, test the
          <code class="literal">birth</code> column:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE birth &gt;= '1998-1-1';</code></strong>
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
</pre><p>
          You can combine conditions, for example, to locate female
          dogs:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';</code></strong>
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
</pre><p>
          The preceding query uses the <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a>
          logical operator. There is also an
          <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a> operator:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';</code></strong>
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+
</pre><p>
          <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a> and
          <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a> may be intermixed, although
          <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a> has higher precedence than
          <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a>. If you use both operators, it
          is a good idea to use parentheses to indicate explicitly how
          conditions should be grouped:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')</code></strong>
       <strong class="userinput"><code>OR (species = 'dog' AND sex = 'f');</code></strong>
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="selecting-columns"></a>3.3.4.3 Selecting Particular Columns</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787940448"></a><a class="indexterm" name="idm140091787938992"></a><p>
          If you do not want to see entire rows from your table, just
          name the columns in which you are interested, separated by
          commas. For example, if you want to know when your animals
          were born, select the <code class="literal">name</code> and
          <code class="literal">birth</code> columns:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth FROM pet;</code></strong>
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
</pre><p>
          To find out who owns pets, use this query:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT owner FROM pet;</code></strong>
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+
</pre><a class="indexterm" name="idm140091787931200"></a><p>
          Notice that the query simply retrieves the
          <code class="literal">owner</code> column from each record, and some of
          them appear more than once. To minimize the output, retrieve
          each unique output record just once by adding the keyword
          <code class="literal">DISTINCT</code>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT DISTINCT owner FROM pet;</code></strong>
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+
</pre><p>
          You can use a <code class="literal">WHERE</code> clause to combine row
          selection with column selection. For example, to get birth
          dates for dogs and cats only, use this query:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, species, birth FROM pet</code></strong>
       <strong class="userinput"><code>WHERE species = 'dog' OR species = 'cat';</code></strong>
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="sorting-rows"></a>3.3.4.4 Sorting Rows</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787921056"></a><a class="indexterm" name="idm140091787919984"></a><a class="indexterm" name="idm140091787918496"></a><a class="indexterm" name="idm140091787917008"></a><a class="indexterm" name="idm140091787915520"></a><p>
          You may have noticed in the preceding examples that the result
          rows are displayed in no particular order. It is often easier
          to examine query output when the rows are sorted in some
          meaningful way. To sort a result, use an <code class="literal">ORDER
          BY</code> clause.
        </p><p>
          Here are animal birthdays, sorted by date:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth FROM pet ORDER BY birth;</code></strong>
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
</pre><p>
          On character type columns, sorting—like all other
          comparison operations—is normally performed in a
          case-insensitive fashion. This means that the order is
          undefined for columns that are identical except for their
          case. You can force a case-sensitive sort for a column by
          using <a class="link" href="functions.html#operator_binary"><code class="literal">BINARY</code></a> like so:
          <code class="literal">ORDER BY BINARY
          <em class="replaceable"><code>col_name</code></em></code>.
        </p><p>
          The default sort order is ascending, with smallest values
          first. To sort in reverse (descending) order, add the
          <code class="literal">DESC</code> keyword to the name of the column you
          are sorting by:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth FROM pet ORDER BY birth DESC;</code></strong>
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+
</pre><p>
          You can sort on multiple columns, and you can sort different
          columns in different directions. For example, to sort by type
          of animal in ascending order, then by birth date within animal
          type in descending order (youngest animals first), use the
          following query:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, species, birth FROM pet</code></strong>
       <strong class="userinput"><code>ORDER BY species, birth DESC;</code></strong>
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+
</pre><p>
          The <code class="literal">DESC</code> keyword applies only to the column
          name immediately preceding it (<code class="literal">birth</code>); it
          does not affect the <code class="literal">species</code> column sort
          order.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="date-calculations"></a>3.3.4.5 Date Calculations</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787895952"></a><a class="indexterm" name="idm140091787894880"></a><a class="indexterm" name="idm140091787893392"></a><a class="indexterm" name="idm140091787891904"></a><p>
          MySQL provides several functions that you can use to perform
          calculations on dates, for example, to calculate ages or
          extract parts of dates.
        </p><p>
          To determine how many years old each of your pets is, use the
          <a class="link" href="functions.html#function_timestampdiff"><code class="literal">TIMESTAMPDIFF()</code></a> function. Its
          arguments are the unit in which you want the result expressed,
          and the two dates for which to take the difference. The
          following query shows, for each pet, the birth date, the
          current date, and the age in years. An
          <span class="emphasis"><em>alias</em></span> (<code class="literal">age</code>) is used to
          make the final output column label more meaningful.
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth, CURDATE(),</code></strong>
       <strong class="userinput"><code>TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age</code></strong>
       <strong class="userinput"><code>FROM pet;</code></strong>
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+
</pre><p>
          The query works, but the result could be scanned more easily
          if the rows were presented in some order. This can be done by
          adding an <code class="literal">ORDER BY name</code> clause to sort the
          output by name:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth, CURDATE(),</code></strong>
       <strong class="userinput"><code>TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age</code></strong>
       <strong class="userinput"><code>FROM pet ORDER BY name;</code></strong>
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+
</pre><p>
          To sort the output by <code class="literal">age</code> rather than
          <code class="literal">name</code>, just use a different <code class="literal">ORDER
          BY</code> clause:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth, CURDATE(),</code></strong>
       <strong class="userinput"><code>TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age</code></strong>
       <strong class="userinput"><code>FROM pet ORDER BY age;</code></strong>
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+
</pre><p>
          A similar query can be used to determine age at death for
          animals that have died. You determine which animals these are
          by checking whether the <code class="literal">death</code> value is
          <code class="literal">NULL</code>. Then, for those with
          non-<code class="literal">NULL</code> values, compute the difference
          between the <code class="literal">death</code> and
          <code class="literal">birth</code> values:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth, death,</code></strong>
       <strong class="userinput"><code>TIMESTAMPDIFF(YEAR,birth,death) AS age</code></strong>
       <strong class="userinput"><code>FROM pet WHERE death IS NOT NULL ORDER BY age;</code></strong>
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+
</pre><p>
          The query uses <code class="literal">death IS NOT NULL</code> rather
          than <code class="literal">death &lt;&gt; NULL</code> because
          <code class="literal">NULL</code> is a special value that cannot be
          compared using the usual comparison operators. This is
          discussed later. See <a class="xref" href="tutorial.html#working-with-null" title="3.3.4.6 Working with NULL Values">Section 3.3.4.6, “Working with NULL Values”</a>.
        </p><p>
          What if you want to know which animals have birthdays next
          month? For this type of calculation, year and day are
          irrelevant; you simply want to extract the month part of the
          <code class="literal">birth</code> column. MySQL provides several
          functions for extracting parts of dates, such as
          <a class="link" href="functions.html#function_year"><code class="literal">YEAR()</code></a>,
          <a class="link" href="functions.html#function_month"><code class="literal">MONTH()</code></a>, and
          <a class="link" href="functions.html#function_dayofmonth"><code class="literal">DAYOFMONTH()</code></a>.
          <a class="link" href="functions.html#function_month"><code class="literal">MONTH()</code></a> is the appropriate
          function here. To see how it works, run a simple query that
          displays the value of both <code class="literal">birth</code> and
          <a class="link" href="functions.html#function_month"><code class="literal">MONTH(birth)</code></a>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth, MONTH(birth) FROM pet;</code></strong>
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+
</pre><p>
          Finding animals with birthdays in the upcoming month is also
          simple. Suppose that the current month is April. Then the
          month value is <code class="literal">4</code> and you can look for
          animals born in May (month <code class="literal">5</code>) like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth FROM pet WHERE MONTH(birth) = 5;</code></strong>
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
</pre><p>
          There is a small complication if the current month is
          December. You cannot merely add one to the month number
          (<code class="literal">12</code>) and look for animals born in month
          <code class="literal">13</code>, because there is no such month.
          Instead, you look for animals born in January (month
          <code class="literal">1</code>).
        </p><p>
          You can write the query so that it works no matter what the
          current month is, so that you do not have to use the number
          for a particular month.
          <a class="link" href="functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a> enables you to add a
          time interval to a given date. If you add a month to the value
          of <a class="link" href="functions.html#function_curdate"><code class="literal">CURDATE()</code></a>, then extract the
          month part with <a class="link" href="functions.html#function_month"><code class="literal">MONTH()</code></a>, the
          result produces the month in which to look for birthdays:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth FROM pet</code></strong>
       <strong class="userinput"><code>WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));</code></strong>
</pre><p>
          A different way to accomplish the same task is to add
          <code class="literal">1</code> to get the next month after the current
          one after using the modulo function (<code class="literal">MOD</code>)
          to wrap the month value to <code class="literal">0</code> if it is
          currently <code class="literal">12</code>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT name, birth FROM pet</code></strong>
       <strong class="userinput"><code>WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;</code></strong>
</pre><p>
          <a class="link" href="functions.html#function_month"><code class="literal">MONTH()</code></a> returns a number
          between <code class="literal">1</code> and <code class="literal">12</code>. And
          <a class="link" href="functions.html#function_mod"><code class="literal">MOD(something,12)</code></a> returns a
          number between <code class="literal">0</code> and <code class="literal">11</code>.
          So the addition has to be after the
          <a class="link" href="functions.html#function_mod"><code class="literal">MOD()</code></a>, otherwise we would go
          from November (<code class="literal">11</code>) to January
          (<code class="literal">1</code>).
        </p><p>
          If a calculation uses invalid dates, the calculation fails and
          produces warnings:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT '2018-10-31' + INTERVAL 1 DAY;</code></strong>
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
mysql&gt; <strong class="userinput"><code>SELECT '2018-10-32' + INTERVAL 1 DAY;</code></strong>
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
mysql&gt; <strong class="userinput"><code>SHOW WARNINGS;</code></strong>
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="working-with-null"></a>3.3.4.6 Working with NULL Values</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787815552"></a><a class="indexterm" name="idm140091787814512"></a><p>
          The <code class="literal">NULL</code> value can be surprising until you
          get used to it. Conceptually, <code class="literal">NULL</code> means
          <span class="quote">“<span class="quote">a missing unknown value</span>”</span> and it is treated
          somewhat differently from other values.
        </p><p>
          To test for <code class="literal">NULL</code>, use the
          <a class="link" href="functions.html#operator_is-null"><code class="literal">IS NULL</code></a> and <a class="link" href="functions.html#operator_is-not-null"><code class="literal">IS
          NOT NULL</code></a> operators, as shown here:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT 1 IS NULL, 1 IS NOT NULL;</code></strong>
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
</pre><p>
          You cannot use arithmetic comparison operators such as
          <a class="link" href="functions.html#operator_equal"><code class="literal">=</code></a>,
          <a class="link" href="functions.html#operator_less-than"><code class="literal">&lt;</code></a>, or
          <a class="link" href="functions.html#operator_not-equal"><code class="literal">&lt;&gt;</code></a> to
          test for <code class="literal">NULL</code>. To demonstrate this for
          yourself, try the following query:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT 1 = NULL, 1 &lt;&gt; NULL, 1 &lt; NULL, 1 &gt; NULL;</code></strong>
+----------+-----------+----------+----------+
| 1 = NULL | 1 &lt;&gt; NULL | 1 &lt; NULL | 1 &gt; NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+
</pre><p>
          Because the result of any arithmetic comparison with
          <code class="literal">NULL</code> is also <code class="literal">NULL</code>, you
          cannot obtain any meaningful results from such comparisons.
        </p><p>
          In MySQL, <code class="literal">0</code> or <code class="literal">NULL</code>
          means false and anything else means true. The default truth
          value from a boolean operation is <code class="literal">1</code>.
        </p><p>
          This special treatment of <code class="literal">NULL</code> is why, in
          the previous section, it was necessary to determine which
          animals are no longer alive using <code class="literal">death IS NOT
          NULL</code> instead of <code class="literal">death &lt;&gt;
          NULL</code>.
        </p><p>
          Two <code class="literal">NULL</code> values are regarded as equal in a
          <code class="literal">GROUP BY</code>.
        </p><p>
          When doing an <code class="literal">ORDER BY</code>,
          <code class="literal">NULL</code> values are presented first if you do
          <code class="literal">ORDER BY ... ASC</code> and last if you do
          <code class="literal">ORDER BY ... DESC</code>.
        </p><p>
          A common error when working with <code class="literal">NULL</code> is to
          assume that it is not possible to insert a zero or an empty
          string into a column defined as <code class="literal">NOT NULL</code>,
          but this is not the case. These are in fact values, whereas
          <code class="literal">NULL</code> means <span class="quote">“<span class="quote">not having a
          value.</span>”</span> You can test this easily enough by using
          <code class="literal">IS [NOT] NULL</code> as shown:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;</code></strong>
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+
</pre><p>
          Thus it is entirely possible to insert a zero or empty string
          into a <code class="literal">NOT NULL</code> column, as these are in
          fact <code class="literal">NOT NULL</code>. See
          <a class="xref" href="error-handling.html#problems-with-null" title="B.4.4.3 Problems with NULL Values">Section B.4.4.3, “Problems with NULL Values”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="pattern-matching"></a>3.3.4.7 Pattern Matching</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787775488"></a><a class="indexterm" name="idm140091787774416"></a><a class="indexterm" name="idm140091787772928"></a><p>
          MySQL provides standard SQL pattern matching as well as a form
          of pattern matching based on extended regular expressions
          similar to those used by Unix utilities such as
          <span class="command"><strong>vi</strong></span>, <span class="command"><strong>grep</strong></span>, and
          <span class="command"><strong>sed</strong></span>.
        </p><p>
          SQL pattern matching enables you to use <code class="literal">_</code>
          to match any single character and <code class="literal">%</code> to
          match an arbitrary number of characters (including zero
          characters). In MySQL, SQL patterns are case-insensitive by
          default. Some examples are shown here. Do not use
          <code class="literal">=</code> or <code class="literal">&lt;&gt;</code> when you
          use SQL patterns. Use the <a class="link" href="functions.html#operator_like"><code class="literal">LIKE</code></a> or
          <a class="link" href="functions.html#operator_not-like"><code class="literal">NOT LIKE</code></a> comparison operators
          instead.
        </p><p>
          To find names beginning with <code class="literal">b</code>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE 'b%';</code></strong>
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
</pre><p>
          To find names ending with <code class="literal">fy</code>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE '%fy';</code></strong>
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
</pre><p>
          To find names containing a <code class="literal">w</code>:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE '%w%';</code></strong>
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
</pre><p>
          To find names containing exactly five characters, use five
          instances of the <code class="literal">_</code> pattern character:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE name LIKE '_____';</code></strong>
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
</pre><p>
          The other type of pattern matching provided by MySQL uses
          extended regular expressions. When you test for a match for
          this type of pattern, use the
          <a class="link" href="functions.html#function_regexp-like"><code class="literal">REGEXP_LIKE()</code></a> function (or the
          <a class="link" href="functions.html#operator_regexp"><code class="literal">REGEXP</code></a> or
          <a class="link" href="functions.html#operator_regexp"><code class="literal">RLIKE</code></a>
          operators, which are synonyms for
          <a class="link" href="functions.html#function_regexp-like"><code class="literal">REGEXP_LIKE()</code></a>).
        </p><p>
          The following list describes some characteristics of extended
          regular expressions:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              <code class="literal">.</code> matches any single character.
            </p></li><li class="listitem"><p>
              A character class <code class="literal">[...]</code> matches any
              character within the brackets. For example,
              <code class="literal">[abc]</code> matches <code class="literal">a</code>,
              <code class="literal">b</code>, or <code class="literal">c</code>. To name a
              range of characters, use a dash. <code class="literal">[a-z]</code>
              matches any letter, whereas <code class="literal">[0-9]</code>
              matches any digit.
            </p></li><li class="listitem"><p>
              <code class="literal">*</code> matches zero or more instances of the
              thing preceding it. For example, <code class="literal">x*</code>
              matches any number of <code class="literal">x</code> characters,
              <code class="literal">[0-9]*</code> matches any number of digits,
              and <code class="literal">.*</code> matches any number of anything.
            </p></li><li class="listitem"><p>
              A regular expression pattern match succeeds if the pattern
              matches anywhere in the value being tested. (This differs
              from a <a class="link" href="functions.html#operator_like"><code class="literal">LIKE</code></a> pattern match,
              which succeeds only if the pattern matches the entire
              value.)
            </p></li><li class="listitem"><p>
              To anchor a pattern so that it must match the beginning or
              end of the value being tested, use <code class="literal">^</code> at
              the beginning or <code class="literal">$</code> at the end of the
              pattern.
</p></li></ul>
</div>
<p>
          To demonstrate how extended regular expressions work, the
          <a class="link" href="functions.html#operator_like"><code class="literal">LIKE</code></a> queries shown previously are
          rewritten here to use
          <a class="link" href="functions.html#function_regexp-like"><code class="literal">REGEXP_LIKE()</code></a>.
        </p><p>
          To find names beginning with <code class="literal">b</code>, use
          <code class="literal">^</code> to match the beginning of the name:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');</code></strong>
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
</pre><p>
          To force a regular expression comparison to be case sensitive,
          use a case-sensitive collation, or use the
          <a class="link" href="functions.html#operator_binary"><code class="literal">BINARY</code></a> keyword to make one of the
          strings a binary string, or specify the <code class="literal">c</code>
          match-control character. Each of these queries matches only
          lowercase <code class="literal">b</code> at the beginning of a name:
        </p><pre data-lang="sql" class="programlisting">
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
</pre><p>
          To find names ending with <code class="literal">fy</code>, use
          <code class="literal">$</code> to match the end of the name:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');</code></strong>
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
</pre><p>
          To find names containing a <code class="literal">w</code>, use this
          query:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');</code></strong>
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
</pre><p>
          Because a regular expression pattern matches if it occurs
          anywhere in the value, it is not necessary in the previous
          query to put a wildcard on either side of the pattern to get
          it to match the entire value as would be true with an SQL
          pattern.
        </p><p>
          To find names containing exactly five characters, use
          <code class="literal">^</code> and <code class="literal">$</code> to match the
          beginning and end of the name, and five instances of
          <code class="literal">.</code> in between:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');</code></strong>
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
</pre><p>
          You could also write the previous query using the
          <code class="literal">{<em class="replaceable"><code>n</code></em>}</code>
          (<span class="quote">“<span class="quote">repeat-<em class="replaceable"><code>n</code></em>-times</span>”</span>)
          operator:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');</code></strong>
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
</pre><p>
          For more information about the syntax for regular expressions,
          see <a class="xref" href="functions.html#regexp" title="12.5.2 Regular Expressions">Section 12.5.2, “Regular Expressions”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="counting-rows"></a>3.3.4.8 Counting Rows</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787696112"></a><a class="indexterm" name="idm140091787694624"></a><a class="indexterm" name="idm140091787693136"></a><p>
          Databases are often used to answer the question, <span class="quote">“<span class="quote">How
          often does a certain type of data occur in a table?</span>”</span>
          For example, you might want to know how many pets you have, or
          how many pets each owner has, or you might want to perform
          various kinds of census operations on your animals.
        </p><p>
          Counting the total number of animals you have is the same
          question as <span class="quote">“<span class="quote">How many rows are in the
          <code class="literal">pet</code> table?</span>”</span> because there is one
          record per pet. <a class="link" href="functions.html#function_count"><code class="literal">COUNT(*)</code></a> counts
          the number of rows, so the query to count your animals looks
          like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT COUNT(*) FROM pet;</code></strong>
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
</pre><p>
          Earlier, you retrieved the names of the people who owned pets.
          You can use <a class="link" href="functions.html#function_count"><code class="literal">COUNT()</code></a> if you want
          to find out how many pets each owner has:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT owner, COUNT(*) FROM pet GROUP BY owner;</code></strong>
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+
</pre><p>
          The preceding query uses <code class="literal">GROUP BY</code> to group
          all records for each <code class="literal">owner</code>. The use of
          <a class="link" href="functions.html#function_count"><code class="literal">COUNT()</code></a> in conjunction with
          <code class="literal">GROUP BY</code> is useful for characterizing your
          data under various groupings. The following examples show
          different ways to perform animal census operations.
        </p><p>
          Number of animals per species:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT species, COUNT(*) FROM pet GROUP BY species;</code></strong>
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+
</pre><p>
          Number of animals per sex:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT sex, COUNT(*) FROM pet GROUP BY sex;</code></strong>
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+
</pre><p>
          (In this output, <code class="literal">NULL</code> indicates that the
          sex is unknown.)
        </p><p>
          Number of animals per combination of species and sex:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;</code></strong>
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+
</pre><p>
          You need not retrieve an entire table when you use
          <a class="link" href="functions.html#function_count"><code class="literal">COUNT()</code></a>. For example, the
          previous query, when performed just on dogs and cats, looks
          like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT species, sex, COUNT(*) FROM pet</code></strong>
       <strong class="userinput"><code>WHERE species = 'dog' OR species = 'cat'</code></strong>
       <strong class="userinput"><code>GROUP BY species, sex;</code></strong>
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+
</pre><p>
          Or, if you wanted the number of animals per sex only for
          animals whose sex is known:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT species, sex, COUNT(*) FROM pet</code></strong>
       <strong class="userinput"><code>WHERE sex IS NOT NULL</code></strong>
       <strong class="userinput"><code>GROUP BY species, sex;</code></strong>
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+
</pre><p>
          If you name columns to select in addition to the
          <a class="link" href="functions.html#function_count"><code class="literal">COUNT()</code></a> value, a <code class="literal">GROUP
          BY</code> clause should be present that names those same
          columns. Otherwise, the following occurs:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              If the
              <a class="link" href="server-administration.html#sqlmode_only_full_group_by"><code class="literal">ONLY_FULL_GROUP_BY</code></a> SQL
              mode is enabled, an error occurs:
            </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET sql_mode = 'ONLY_FULL_GROUP_BY';</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT owner, COUNT(*) FROM pet;</code></strong>
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by
</pre></li><li class="listitem"><p>
              If <a class="link" href="server-administration.html#sqlmode_only_full_group_by"><code class="literal">ONLY_FULL_GROUP_BY</code></a> is
              not enabled, the query is processed by treating all rows
              as a single group, but the value selected for each named
              column is nondeterministic. The server is free to select
              the value from any row:
            </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET sql_mode = '';</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT owner, COUNT(*) FROM pet;</code></strong>
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        8 |
+--------+----------+
1 row in set (0.00 sec)
</pre></li></ul>
</div>
<p>
          See also <a class="xref" href="functions.html#group-by-handling" title="12.20.3 MySQL Handling of GROUP BY">Section 12.20.3, “MySQL Handling of GROUP BY”</a>. See
          <a class="xref" href="functions.html#group-by-functions" title="12.20.1 Aggregate (GROUP BY) Function Descriptions">Section 12.20.1, “Aggregate (GROUP BY) Function Descriptions”</a> for information about
          <a class="link" href="functions.html#function_count"><code class="literal">COUNT(<em class="replaceable"><code>expr</code></em>)</code></a>
          behavior and related optimizations.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="multiple-tables"></a>3.3.4.9 Using More Than one Table</h4>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787643024"></a><p>
          The <code class="literal">pet</code> table keeps track of which pets you
          have. If you want to record other information about them, such
          as events in their lives like visits to the vet or when
          litters are born, you need another table. What should this
          table look like? It needs to contain the following
          information:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The pet name so that you know which animal each event
              pertains to.
            </p></li><li class="listitem"><p>
              A date so that you know when the event occurred.
            </p></li><li class="listitem"><p>
              A field to describe the event.
            </p></li><li class="listitem"><p>
              An event type field, if you want to be able to categorize
              events.
</p></li></ul>
</div>
<p>
          Given these considerations, the <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
          TABLE</code></a> statement for the <code class="literal">event</code>
          table might look like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE event (name VARCHAR(20), date DATE,</code></strong>
       <strong class="userinput"><code>type VARCHAR(15), remark VARCHAR(255));</code></strong>
</pre><p>
          As with the <code class="literal">pet</code> table, it is easiest to
          load the initial records by creating a tab-delimited text file
          containing the following information.
</p>
<div class="informaltable">
<table summary="pet record data that will appear in a tab delimited text file, as described in the preceding text."><col width="15%"><col width="15%"><col width="15%"><col width="35%"><thead><tr>
              <th scope="col">name</th>
              <th scope="col">date</th>
              <th scope="col">type</th>
              <th scope="col">remark</th>
            </tr></thead><tbody><tr>
              <td scope="row">Fluffy</td>
              <td>1995-05-15</td>
              <td>litter</td>
              <td>4 kittens, 3 female, 1 male</td>
            </tr><tr>
              <td scope="row">Buffy</td>
              <td>1993-06-23</td>
              <td>litter</td>
              <td>5 puppies, 2 female, 3 male</td>
            </tr><tr>
              <td scope="row">Buffy</td>
              <td>1994-06-19</td>
              <td>litter</td>
              <td>3 puppies, 3 female</td>
            </tr><tr>
              <td scope="row">Chirpy</td>
              <td>1999-03-21</td>
              <td>vet</td>
              <td>needed beak straightened</td>
            </tr><tr>
              <td scope="row">Slim</td>
              <td>1997-08-03</td>
              <td>vet</td>
              <td>broken rib</td>
            </tr><tr>
              <td scope="row">Bowser</td>
              <td>1991-10-12</td>
              <td>kennel</td>
              <td></td>
            </tr><tr>
              <td scope="row">Fang</td>
              <td>1991-10-12</td>
              <td>kennel</td>
              <td></td>
            </tr><tr>
              <td scope="row">Fang</td>
              <td>1998-08-28</td>
              <td>birthday</td>
              <td>Gave him a new chew toy</td>
            </tr><tr>
              <td scope="row">Claws</td>
              <td>1998-03-17</td>
              <td>birthday</td>
              <td>Gave him a new flea collar</td>
            </tr><tr>
              <td scope="row">Whistler</td>
              <td>1998-12-09</td>
              <td>birthday</td>
              <td>First birthday</td>
</tr></tbody></table>
</div>
<p>
          Load the records like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;</code></strong>
</pre><p>
          Based on what you have learned from the queries that you have
          run on the <code class="literal">pet</code> table, you should be able to
          perform retrievals on the records in the
          <code class="literal">event</code> table; the principles are the same.
          But when is the <code class="literal">event</code> table by itself
          insufficient to answer questions you might ask?
        </p><p>
          Suppose that you want to find out the ages at which each pet
          had its litters. We saw earlier how to calculate ages from two
          dates. The litter date of the mother is in the
          <code class="literal">event</code> table, but to calculate her age on
          that date you need her birth date, which is stored in the
          <code class="literal">pet</code> table. This means the query requires
          both tables:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT pet.name,</code></strong>
       <strong class="userinput"><code>TIMESTAMPDIFF(YEAR,birth,date) AS age,</code></strong>
       <strong class="userinput"><code>remark</code></strong>
       <strong class="userinput"><code>FROM pet INNER JOIN event</code></strong>
         <strong class="userinput"><code>ON pet.name = event.name</code></strong>
       <strong class="userinput"><code>WHERE event.type = 'litter';</code></strong>
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+
</pre><p>
          There are several things to note about this query:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              The <code class="literal">FROM</code> clause joins two tables
              because the query needs to pull information from both of
              them.
            </p></li><li class="listitem"><p>
              When combining (joining) information from multiple tables,
              you need to specify how records in one table can be
              matched to records in the other. This is easy because they
              both have a <code class="literal">name</code> column. The query uses
              an <code class="literal">ON</code> clause to match up records in the
              two tables based on the <code class="literal">name</code> values.
            </p><p>
              The query uses an <code class="literal">INNER JOIN</code> to combine
              the tables. An <code class="literal">INNER JOIN</code> permits rows
              from either table to appear in the result if and only if
              both tables meet the conditions specified in the
              <code class="option">ON</code> clause. In this example, the
              <code class="literal">ON</code> clause specifies that the
              <code class="literal">name</code> column in the
              <code class="literal">pet</code> table must match the
              <code class="literal">name</code> column in the
              <code class="literal">event</code> table. If a name appears in one
              table but not the other, the row will not appear in the
              result because the condition in the <code class="literal">ON</code>
              clause fails.
            </p></li><li class="listitem"><p>
              Because the <code class="literal">name</code> column occurs in both
              tables, you must be specific about which table you mean
              when referring to the column. This is done by prepending
              the table name to the column name.
</p></li></ul>
</div>
<p>
          You need not have two different tables to perform a join.
          Sometimes it is useful to join a table to itself, if you want
          to compare records in a table to other records in that same
          table. For example, to find breeding pairs among your pets,
          you can join the <code class="literal">pet</code> table with itself to
          produce candidate pairs of live males and females of like
          species:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species</code></strong>
       <strong class="userinput"><code>FROM pet AS p1 INNER JOIN pet AS p2</code></strong>
         <strong class="userinput"><code>ON p1.species = p2.species</code></strong>
         <strong class="userinput"><code>AND p1.sex = 'f' AND p1.death IS NULL</code></strong>
         <strong class="userinput"><code>AND p2.sex = 'm' AND p2.death IS NULL;</code></strong>
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+
</pre><p>
          In this query, we specify aliases for the table name to refer
          to the columns and keep straight which instance of the table
          each column reference is associated with.
</p>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="getting-information"></a>3.4 Getting Information About Databases and Tables</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787543568"></a><a class="indexterm" name="idm140091787542112"></a><a class="indexterm" name="idm140091787540624"></a><p>
      What if you forget the name of a database or table, or what the
      structure of a given table is (for example, what its columns are
      called)? MySQL addresses this problem through several statements
      that provide information about the databases and tables it
      supports.
    </p><p>
      You have previously seen <a class="link" href="sql-syntax.html#show-databases" title="13.7.6.14 SHOW DATABASES Syntax"><code class="literal">SHOW
      DATABASES</code></a>, which lists the databases managed by the
      server. To find out which database is currently selected, use the
      <a class="link" href="functions.html#function_database"><code class="literal">DATABASE()</code></a> function:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT DATABASE();</code></strong>
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+
</pre><p>
      If you have not yet selected any database, the result is
      <code class="literal">NULL</code>.
    </p><p>
      To find out what tables the default database contains (for
      example, when you are not sure about the name of a table), use
      this statement:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW TABLES;</code></strong>
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+
</pre><p>
      The name of the column in the output produced by this statement is
      always
      <code class="literal">Tables_in_<em class="replaceable"><code>db_name</code></em></code>,
      where <em class="replaceable"><code>db_name</code></em> is the name of the
      database. See <a class="xref" href="sql-syntax.html#show-tables" title="13.7.6.37 SHOW TABLES Syntax">Section 13.7.6.37, “SHOW TABLES Syntax”</a>, for more information.
    </p><p>
      If you want to find out about the structure of a table, the
      <a class="link" href="sql-syntax.html#describe" title="13.8.1 DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a> statement is useful; it
      displays information about each of a table's columns:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>DESCRIBE pet;</code></strong>
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
</pre><p>
      <code class="literal">Field</code> indicates the column name,
      <code class="literal">Type</code> is the data type for the column,
      <code class="literal">NULL</code> indicates whether the column can contain
      <code class="literal">NULL</code> values, <code class="literal">Key</code> indicates
      whether the column is indexed, and <code class="literal">Default</code>
      specifies the column's default value. <code class="literal">Extra</code>
      displays special information about columns: If a column was
      created with the <code class="literal">AUTO_INCREMENT</code> option, the
      value will be <code class="literal">auto_increment</code> rather than empty.
    </p><p>
      <code class="literal">DESC</code> is a short form of
      <a class="link" href="sql-syntax.html#describe" title="13.8.1 DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a>. See
      <a class="xref" href="sql-syntax.html#describe" title="13.8.1 DESCRIBE Syntax">Section 13.8.1, “DESCRIBE Syntax”</a>, for more information.
    </p><p>
      You can obtain the <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
      statement necessary to create an existing table using the
      <a class="link" href="sql-syntax.html#show-create-table" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a> statement. See
      <a class="xref" href="sql-syntax.html#show-create-table" title="13.7.6.10 SHOW CREATE TABLE Syntax">Section 13.7.6.10, “SHOW CREATE TABLE Syntax”</a>.
    </p><p>
      If you have indexes on a table, <code class="literal">SHOW INDEX FROM
      <em class="replaceable"><code>tbl_name</code></em></code> produces information
      about them. See <a class="xref" href="sql-syntax.html#show-index" title="13.7.6.22 SHOW INDEX Syntax">Section 13.7.6.22, “SHOW INDEX Syntax”</a>, for more about this
      statement.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="batch-mode"></a>3.5 Using mysql in Batch Mode</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787506528"></a><a class="indexterm" name="idm140091787505072"></a><a class="indexterm" name="idm140091787504000"></a><a class="indexterm" name="idm140091787502512"></a><a class="indexterm" name="idm140091787501440"></a><a class="indexterm" name="idm140091787499952"></a><p>
      In the previous sections, you used <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a>
      interactively to enter statements and view the results. You can
      also run <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> in batch mode. To do this, put
      the statements you want to run in a file, then tell
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> to read its input from the file:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; <em class="replaceable"><code>batch-file</code></em></code></strong>
</pre><p>
      If you are running <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> under Windows and have
      some special characters in the file that cause problems, you can
      do this:
    </p><pre data-lang="terminal" class="programlisting">
C:\&gt; <strong class="userinput"><code>mysql -e "source <em class="replaceable"><code>batch-file</code></em>"</code></strong>
</pre><p>
      If you need to specify connection parameters on the command line,
      the command might look like this:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql -h <em class="replaceable"><code>host</code></em> -u <em class="replaceable"><code>user</code></em> -p &lt; <em class="replaceable"><code>batch-file</code></em></code></strong>
Enter password: <strong class="userinput"><code>********</code></strong>
</pre><p>
      When you use <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> this way, you are creating a
      script file, then executing the script.
    </p><p>
      If you want the script to continue even if some of the statements
      in it produce errors, you should use the
      <a class="link" href="programs.html#option_mysql_force"><code class="option">--force</code></a> command-line option.
    </p><p>
      Why use a script? Here are a few reasons:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          If you run a query repeatedly (say, every day or every week),
          making it a script enables you to avoid retyping it each time
          you execute it.
        </p></li><li class="listitem"><p>
          You can generate new queries from existing ones that are
          similar by copying and editing script files.
        </p></li><li class="listitem"><p>
          Batch mode can also be useful while you're developing a query,
          particularly for multiple-line statements or
          multiple-statement sequences. If you make a mistake, you don't
          have to retype everything. Just edit your script to correct
          the error, then tell <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> to execute it
          again.
        </p></li><li class="listitem"><p>
          If you have a query that produces a lot of output, you can run
          the output through a pager rather than watching it scroll off
          the top of your screen:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; <em class="replaceable"><code>batch-file</code></em> | more</code></strong>
</pre></li><li class="listitem"><p>
          You can catch the output in a file for further processing:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; <em class="replaceable"><code>batch-file</code></em> &gt; mysql.out</code></strong>
</pre></li><li class="listitem"><p>
          You can distribute your script to other people so that they
          can also run the statements.
        </p></li><li class="listitem"><p>
          Some situations do not allow for interactive use, for example,
          when you run a query from a <span class="command"><strong>cron</strong></span> job. In
          this case, you must use batch mode.
</p></li></ul>
</div>
<p>
      The default output format is different (more concise) when you run
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> in batch mode than when you use it
      interactively. For example, the output of <code class="literal">SELECT DISTINCT
      species FROM pet</code> looks like this when
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> is run interactively:
    </p><pre data-lang="none" class="programlisting">
+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+
</pre><p>
      In batch mode, the output looks like this instead:
    </p><pre data-lang="none" class="programlisting">
species
bird
cat
dog
hamster
snake
</pre><p>
      If you want to get the interactive output format in batch mode,
      use <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql -t</strong></span></a>. To echo to the output the
      statements that are executed, use <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql -v</strong></span></a>.
    </p><a class="indexterm" name="idm140091787458464"></a><a class="indexterm" name="idm140091787457376"></a><a class="indexterm" name="idm140091787456288"></a><a class="indexterm" name="idm140091787455184"></a><p>
      You can also use scripts from the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> prompt
      by using the <code class="literal">source</code> command or
      <code class="literal">\.</code> command:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>source <em class="replaceable"><code>filename</code></em>;</code></strong>
mysql&gt; <strong class="userinput"><code>\. <em class="replaceable"><code>filename</code></em></code></strong>
</pre><p>
      See <a class="xref" href="programs.html#mysql-batch-commands" title="4.5.1.5 Executing SQL Statements from a Text File">Section 4.5.1.5, “Executing SQL Statements from a Text File”</a>, for more information.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="examples"></a>3.6 Examples of Common Queries</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="tutorial.html#example-maximum-column">3.6.1 The Maximum Value for a Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-row">3.6.2 The Row Holding the Maximum of a Certain Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group">3.6.3 Maximum of Column per Group</a></span></dt><dt><span class="section"><a href="tutorial.html#example-maximum-column-group-row">3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column</a></span></dt><dt><span class="section"><a href="tutorial.html#example-user-variables">3.6.5 Using User-Defined Variables</a></span></dt><dt><span class="section"><a href="tutorial.html#example-foreign-keys">3.6.6 Using Foreign Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#searching-on-two-keys">3.6.7 Searching on Two Keys</a></span></dt><dt><span class="section"><a href="tutorial.html#calculating-days">3.6.8 Calculating Visits Per Day</a></span></dt><dt><span class="section"><a href="tutorial.html#example-auto-increment">3.6.9 Using AUTO_INCREMENT</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140091787445376"></a><a class="indexterm" name="idm140091787443920"></a><p>
      Here are examples of how to solve some common problems with MySQL.
    </p><p>
      Some of the examples use the table <code class="literal">shop</code> to hold
      the price of each article (item number) for certain traders
      (dealers). Supposing that each trader has a single fixed price per
      article, then (<code class="literal">article</code>,
      <code class="literal">dealer</code>) is a primary key for the records.
    </p><p>
      Start the command-line tool <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Client"><span class="command"><strong>mysql</strong></span></a> and select a
      database:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql <em class="replaceable"><code>your-database-name</code></em></code></strong>
</pre><p>
      To create and populate the example table, use these statements:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE shop (
    article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
</pre><p>
      After issuing the statements, the table should have the following
      contents:
    </p><pre data-lang="sql" class="programlisting">
SELECT * FROM shop ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+
</pre>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-maximum-column"></a>3.6.1 The Maximum Value for a Column</h3>
</div>
</div>
</div>
<p>
        <span class="quote">“<span class="quote">What is the highest item number?</span>”</span>
      </p><pre data-lang="sql" class="programlisting">
SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-maximum-row"></a>3.6.2 The Row Holding the Maximum of a Certain Column</h3>

</div>

</div>

</div>
<p>
        <span class="emphasis"><em>Task: Find the number, dealer, and price of the most
        expensive article.</em></span>
      </p><p>
        This is easily done with a subquery:
      </p><pre data-lang="sql" class="programlisting">
SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
</pre><p>
        Other solutions are to use a <code class="literal">LEFT JOIN</code> or to
        sort all rows descending by price and get only the first row
        using the MySQL-specific <code class="literal">LIMIT</code> clause:
      </p><pre data-lang="sql" class="programlisting">
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price &lt; s2.price
WHERE s2.article IS NULL;

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
</pre>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          If there were several most expensive articles, each with a
          price of 19.95, the <code class="literal">LIMIT</code> solution would
          show only one of them.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-maximum-column-group"></a>3.6.3 Maximum of Column per Group</h3>

</div>

</div>

</div>
<p>
        <span class="emphasis"><em>Task: Find the highest price per article.</em></span>
      </p><pre data-lang="sql" class="programlisting">
SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article
ORDER BY article;

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-maximum-column-group-row"></a>3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column</h3>

</div>

</div>

</div>
<p>
        <span class="emphasis"><em>Task: For each article, find the dealer or dealers
        with the most expensive price.</em></span>
      </p><p>
        This problem can be solved with a subquery like this one:
      </p><pre data-lang="sql" class="programlisting">
SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)
ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
</pre><p>
        The preceding example uses a correlated subquery, which can be
        inefficient (see <a class="xref" href="sql-syntax.html#correlated-subqueries" title="13.2.11.7 Correlated Subqueries">Section 13.2.11.7, “Correlated Subqueries”</a>). Other
        possibilities for solving the problem are to use an uncorrelated
        subquery in the <code class="literal">FROM</code> clause, a <code class="literal">LEFT
        JOIN</code>, or a common table expression with a window
        function.
      </p><p>
        Uncorrelated subquery:
      </p><pre data-lang="sql" class="programlisting">
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;
</pre><p>
        <code class="literal">LEFT JOIN</code>:
      </p><pre data-lang="sql" class="programlisting">
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price &lt; s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;
</pre><p>
        The <code class="literal">LEFT JOIN</code> works on the basis that when
        <code class="literal">s1.price</code> is at its maximum value, there is no
        <code class="literal">s2.price</code> with a greater value and thus the
        corresponding <code class="literal">s2.article</code> value is
        <code class="literal">NULL</code>. See <a class="xref" href="sql-syntax.html#join" title="13.2.10.2 JOIN Syntax">Section 13.2.10.2, “JOIN Syntax”</a>.
      </p><p>
        Common table expression with window function:
      </p><a class="indexterm" name="idm140091787400976"></a><a class="indexterm" name="idm140091787399888"></a><pre data-lang="sql" class="programlisting">
WITH s1 AS (
   SELECT article, dealer, price,
          RANK() OVER (PARTITION BY article
                           ORDER BY price DESC
                      ) AS `Rank`
     FROM shop
)
SELECT article, dealer, price
  FROM s1
  WHERE `Rank` = 1
ORDER BY article;
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-user-variables"></a>3.6.5 Using User-Defined Variables</h3>

</div>

</div>

</div>
<p>
        You can employ MySQL user variables to remember results without
        having to store them in temporary variables in the client. (See
        <a class="xref" href="language-structure.html#user-variables" title="9.4 User-Defined Variables">Section 9.4, “User-Defined Variables”</a>.)
      </p><p>
        For example, to find the articles with the highest and lowest
        price you can do this:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT * FROM shop WHERE price=@min_price OR price=@max_price;</code></strong>
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
</pre>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          It is also possible to store the name of a database object
          such as a table or a column in a user variable and then to use
          this variable in an SQL statement; however, this requires the
          use of a prepared statement. See
          <a class="xref" href="sql-syntax.html#sql-syntax-prepared-statements" title="13.5 Prepared SQL Statement Syntax">Section 13.5, “Prepared SQL Statement Syntax”</a>, for more
          information.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-foreign-keys"></a>3.6.6 Using Foreign Keys</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787388784"></a><a class="indexterm" name="idm140091787387712"></a><p>
        In MySQL, <code class="literal">InnoDB</code> tables support checking of
        foreign key constraints. See
        <a class="xref" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine">Chapter 15, <i>The InnoDB Storage Engine</i></a>, and
        <a class="xref" href="introduction.html#ansi-diff-foreign-keys" title="1.8.2.3 Foreign Key Differences">Section 1.8.2.3, “Foreign Key Differences”</a>.
      </p><p>
        A foreign key constraint is not required merely to join two
        tables. For storage engines other than
        <code class="literal">InnoDB</code>, it is possible when defining a column
        to use a <code class="literal">REFERENCES
        <em class="replaceable"><code>tbl_name</code></em>(<em class="replaceable"><code>col_name</code></em>)</code>
        clause, which has no actual effect, and <span class="emphasis"><em>serves only as
        a memo or comment to you that the column which you are currently
        defining is intended to refer to a column in another
        table</em></span>. It is extremely important to realize when
        using this syntax that:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            MySQL does not perform any sort of check to make sure that
            <em class="replaceable"><code>col_name</code></em> actually exists in
            <em class="replaceable"><code>tbl_name</code></em> (or even that
            <em class="replaceable"><code>tbl_name</code></em> itself exists).
          </p></li><li class="listitem"><p>
            MySQL does not perform any sort of action on
            <em class="replaceable"><code>tbl_name</code></em> such as deleting rows in
            response to actions taken on rows in the table which you are
            defining; in other words, this syntax induces no <code class="literal">ON
            DELETE</code> or <code class="literal">ON UPDATE</code> behavior
            whatsoever. (Although you can write an <code class="literal">ON
            DELETE</code> or <code class="literal">ON UPDATE</code> clause as
            part of the <code class="literal">REFERENCES</code> clause, it is also
            ignored.)
          </p></li><li class="listitem"><p>
            This syntax creates a <span class="emphasis"><em>column</em></span>; it does
            <span class="bold"><strong>not</strong></span> create any sort of
            index or key.
</p></li></ul>
</div>
<p>
        You can use a column so created as a join column, as shown here:
      </p><pre data-lang="sql" class="programlisting">
CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p INNER JOIN shirt s
   ON s.owner = p.id
 WHERE p.name LIKE 'Lilliana%'
   AND s.color &lt;&gt; 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+
</pre><p>
        When used in this fashion, the <code class="literal">REFERENCES</code>
        clause is not displayed in the output of
        <a class="link" href="sql-syntax.html#show-create-table" title="13.7.6.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a> or
        <a class="link" href="sql-syntax.html#describe" title="13.8.1 DESCRIBE Syntax"><code class="literal">DESCRIBE</code></a>:
      </p><pre data-lang="sql" class="programlisting">
SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
</pre><p>
        The use of <code class="literal">REFERENCES</code> in this way as a
        comment or <span class="quote">“<span class="quote">reminder</span>”</span> in a column definition works
        with <code class="literal">MyISAM</code> tables.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="searching-on-two-keys"></a>3.6.7 Searching on Two Keys</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787357184"></a><a class="indexterm" name="idm140091787356144"></a><a class="indexterm" name="idm140091787355072"></a><a class="indexterm" name="idm140091787353584"></a><p>
        An <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a> using a single key is well
        optimized, as is the handling of
        <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a>.
      </p><p>
        The one tricky case is that of searching on two different keys
        combined with <a class="link" href="functions.html#operator_or"><code class="literal">OR</code></a>:
      </p><pre data-lang="sql" class="programlisting">
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'
</pre><p>
        This case is optimized. See
        <a class="xref" href="optimization.html#index-merge-optimization" title="8.2.1.3 Index Merge Optimization">Section 8.2.1.3, “Index Merge Optimization”</a>.
      </p><p>
        You can also solve the problem efficiently by using a
        <a class="link" href="sql-syntax.html#union" title="13.2.10.3 UNION Syntax"><code class="literal">UNION</code></a> that combines the output of
        two separate <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statements.
        See <a class="xref" href="sql-syntax.html#union" title="13.2.10.3 UNION Syntax">Section 13.2.10.3, “UNION Syntax”</a>.
      </p><p>
        Each <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> searches only one key
        and can be optimized:
      </p><pre data-lang="sql" class="programlisting">
SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="calculating-days"></a>3.6.8 Calculating Visits Per Day</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787337424"></a><a class="indexterm" name="idm140091787336384"></a><a class="indexterm" name="idm140091787335312"></a><a class="indexterm" name="idm140091787334224"></a><p>
        The following example shows how you can use the bit group
        functions to calculate the number of days per month a user has
        visited a Web page.
      </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 (year YEAR(4), month INT UNSIGNED,
             day INT UNSIGNED);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);
</pre><p>
        The example table contains year-month-day values representing
        visits by users to the page. To determine how many different
        days in each month these visits occur, use this query:
      </p><pre data-lang="sql" class="programlisting">
SELECT year,month,BIT_COUNT(BIT_OR(1&lt;&lt;day)) AS days FROM t1
       GROUP BY year,month;
</pre><p>
        Which returns:
      </p><pre data-lang="none" class="programlisting">
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+
</pre><p>
        The query calculates how many different days appear in the table
        for each year/month combination, with automatic removal of
        duplicate entries.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="example-auto-increment"></a>3.6.9 Using AUTO_INCREMENT</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787325104"></a><a class="indexterm" name="idm140091787324032"></a><a class="indexterm" name="idm140091787322960"></a><p>
        The <code class="literal">AUTO_INCREMENT</code> attribute can be used to
        generate a unique identity for new rows:
      </p><pre data-lang="sql" class="programlisting">
CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
</pre><p>
        Which returns:
      </p><pre data-lang="none" class="programlisting">
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
</pre><p>
        No value was specified for the <code class="literal">AUTO_INCREMENT</code>
        column, so MySQL assigned sequence numbers automatically. You
        can also explicitly assign 0 to the column to generate sequence
        numbers, unless the
        <a class="link" href="server-administration.html#sqlmode_no_auto_value_on_zero"><code class="literal">NO_AUTO_VALUE_ON_ZERO</code></a> SQL mode
        is enabled. For example:
      </p><pre data-lang="sql" class="programlisting">
INSERT INTO animals (id,name) VALUES(0,'groundhog');
</pre><p>
        If the column is declared <code class="literal">NOT NULL</code>, it is
        also possible to assign <code class="literal">NULL</code> to the column to
        generate sequence numbers. For example:
      </p><pre data-lang="sql" class="programlisting">
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
</pre><p>
        When you insert any other value into an
        <code class="literal">AUTO_INCREMENT</code> column, the column is set to
        that value and the sequence is reset so that the next
        automatically generated value follows sequentially from the
        largest column value. For example:
      </p><pre data-lang="sql" class="programlisting">
<strong class="userinput"><code>INSERT INTO animals (id,name) VALUES(100,'rabbit');</code></strong>
<strong class="userinput"><code>INSERT INTO animals (id,name) VALUES(NULL,'mouse');</code></strong>
<strong class="userinput"><code>SELECT * FROM animals;</code></strong>
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+
</pre><p>
        Updating an existing <code class="literal">AUTO_INCREMENT</code> column
        value also resets the <code class="literal">AUTO_INCREMENT</code>
        sequence.
      </p><p>
        You can retrieve the most recent automatically generated
        <code class="literal">AUTO_INCREMENT</code> value with the
        <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> SQL function or
        the <a class="link" href="connectors-apis.html#mysql-insert-id" title="28.7.7.38 mysql_insert_id()"><code class="literal">mysql_insert_id()</code></a> C API
        function. These functions are connection-specific, so their
        return values are not affected by another connection which is
        also performing inserts.
      </p><p>
        Use the smallest integer data type for the
        <code class="literal">AUTO_INCREMENT</code> column that is large enough to
        hold the maximum sequence value you will need. When the column
        reaches the upper limit of the data type, the next attempt to
        generate a sequence number fails. Use the
        <code class="literal">UNSIGNED</code> attribute if possible to allow a
        greater range. For example, if you use
        <a class="link" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"><code class="literal">TINYINT</code></a>, the maximum permissible
        sequence number is 127. For
        <a class="link" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT"><code class="literal">TINYINT
        UNSIGNED</code></a>, the maximum is 255. See
        <a class="xref" href="data-types.html#integer-types" title="11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">Section 11.2.1, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT,
        MEDIUMINT, BIGINT”</a> for the ranges of all the
        integer types.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          For a multiple-row insert,
          <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> and
          <a class="link" href="connectors-apis.html#mysql-insert-id" title="28.7.7.38 mysql_insert_id()"><code class="literal">mysql_insert_id()</code></a> actually
          return the <code class="literal">AUTO_INCREMENT</code> key from the
          <span class="emphasis"><em>first</em></span> of the inserted rows. This enables
          multiple-row inserts to be reproduced correctly on other
          servers in a replication setup.
</p>
</div>
<p>
        To start with an <code class="literal">AUTO_INCREMENT</code> value other
        than 1, set that value with <a class="link" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax"><code class="literal">CREATE
        TABLE</code></a> or <a class="link" href="sql-syntax.html#alter-table" title="13.1.9 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>,
        like this:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>ALTER TABLE tbl AUTO_INCREMENT = 100;</code></strong>
</pre>
<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h4 class="title"><a name="example-auto-increment-innodb-notes"></a>InnoDB Notes</h4>
</div>
</div>
</div>
<p>
          For information about <code class="literal">AUTO_INCREMENT</code> usage
          specific to <code class="literal">InnoDB</code>, see
          <a class="xref" href="innodb-storage-engine.html#innodb-auto-increment-handling" title="15.6.1.4 AUTO_INCREMENT Handling in InnoDB">Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB”</a>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="example-auto-increment-myisam-notes"></a>MyISAM Notes</h4>

</div>

</div>

</div>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              For <code class="literal">MyISAM</code> tables, you can specify
              <code class="literal">AUTO_INCREMENT</code> on a secondary column in
              a multiple-column index. In this case, the generated value
              for the <code class="literal">AUTO_INCREMENT</code> column is
              calculated as
              <a class="link" href="functions.html#function_max"><code class="literal">MAX(<em class="replaceable"><code>auto_increment_column</code></em>)
              + 1 WHERE
              prefix=<em class="replaceable"><code>given-prefix</code></em></code></a>.
              This is useful when you want to put data into ordered
              groups.
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
</pre><p>
              Which returns:
            </p><pre data-lang="none" class="programlisting">
+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+
</pre><p>
              In this case (when the <code class="literal">AUTO_INCREMENT</code>
              column is part of a multiple-column index),
              <code class="literal">AUTO_INCREMENT</code> values are reused if you
              delete the row with the biggest
              <code class="literal">AUTO_INCREMENT</code> value in any group. This
              happens even for <code class="literal">MyISAM</code> tables, for
              which <code class="literal">AUTO_INCREMENT</code> values normally
              are not reused.
            </p></li><li class="listitem"><p>
              If the <code class="literal">AUTO_INCREMENT</code> column is part of
              multiple indexes, MySQL generates sequence values using
              the index that begins with the
              <code class="literal">AUTO_INCREMENT</code> column, if there is one.
              For example, if the <code class="literal">animals</code> table
              contained indexes <code class="literal">PRIMARY KEY (grp, id)</code>
              and <code class="literal">INDEX (id)</code>, MySQL would ignore the
              <code class="literal">PRIMARY KEY</code> for generating sequence
              values. As a result, the table would contain a single
              sequence, not a sequence per <code class="literal">grp</code> value.
</p></li></ul>
</div>

</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h4 class="title"><a name="example-auto-increment-further-reading"></a>Further Reading</h4>

</div>

</div>

</div>
<p>
          More information about <code class="literal">AUTO_INCREMENT</code> is
          available here:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              How to assign the <code class="literal">AUTO_INCREMENT</code>
              attribute to a column: <a class="xref" href="sql-syntax.html#create-table" title="13.1.20 CREATE TABLE Syntax">Section 13.1.20, “CREATE TABLE Syntax”</a>, and
              <a class="xref" href="sql-syntax.html#alter-table" title="13.1.9 ALTER TABLE Syntax">Section 13.1.9, “ALTER TABLE Syntax”</a>.
            </p></li><li class="listitem"><p>
              How <code class="literal">AUTO_INCREMENT</code> behaves depending on
              the
              <a class="link" href="server-administration.html#sqlmode_no_auto_value_on_zero"><code class="literal">NO_AUTO_VALUE_ON_ZERO</code></a>
              SQL mode: <a class="xref" href="server-administration.html#sql-mode" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>.
            </p></li><li class="listitem"><p>
              How to use the
              <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> function
              to find the row that contains the most recent
              <code class="literal">AUTO_INCREMENT</code> value:
              <a class="xref" href="functions.html#information-functions" title="12.15 Information Functions">Section 12.15, “Information Functions”</a>.
            </p></li><li class="listitem"><p>
              Setting the <code class="literal">AUTO_INCREMENT</code> value to be
              used: <a class="xref" href="server-administration.html#server-system-variables" title="5.1.8 Server System Variables">Section 5.1.8, “Server System Variables”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="innodb-storage-engine.html#innodb-auto-increment-handling" title="15.6.1.4 AUTO_INCREMENT Handling in InnoDB">Section 15.6.1.4, “AUTO_INCREMENT Handling in InnoDB”</a>
            </p></li><li class="listitem"><p>
              <code class="literal">AUTO_INCREMENT</code> and replication:
              <a class="xref" href="replication.html#replication-features-auto-increment" title="17.4.1.1 Replication and AUTO_INCREMENT">Section 17.4.1.1, “Replication and AUTO_INCREMENT”</a>.
            </p></li><li class="listitem"><p>
              Server-system variables related to
              <code class="literal">AUTO_INCREMENT</code>
              (<a class="link" href="replication.html#sysvar_auto_increment_increment"><code class="literal">auto_increment_increment</code></a>
              and
              <a class="link" href="replication.html#sysvar_auto_increment_offset"><code class="literal">auto_increment_offset</code></a>)
              that can be used for replication:
              <a class="xref" href="server-administration.html#server-system-variables" title="5.1.8 Server System Variables">Section 5.1.8, “Server System Variables”</a>.
</p></li></ul>
</div>

</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="apache"></a>3.7 Using MySQL with Apache</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm140091787232688"></a><p>
      There are programs that let you authenticate your users from a
      MySQL database and also let you write your log files into a MySQL
      table.
    </p><p>
      You can change the Apache logging format to be easily readable by
      MySQL by putting the following into the Apache configuration file:
    </p><pre data-lang="none" class="programlisting">
LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%&gt;s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""
</pre><p>
      To load a log file in that format into MySQL, you can use a
      statement something like this:
    </p><pre data-lang="sql" class="programlisting">
LOAD DATA INFILE '<em class="replaceable"><code>/local/access_log</code></em>' INTO TABLE <em class="replaceable"><code>tbl_name</code></em>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
</pre><p>
      The named table should be created to have columns that correspond
      to those that the <code class="literal">LogFormat</code> line writes to the
      log file.
</p>
</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="installing.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="programs.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 2 Installing and Upgrading MySQL</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Chapter 4 MySQL Programs</td>
</tr>
</table>
</div>
</body>
</html>
